# 操作地址，温度，湿度数据库表
import datetime as datetime

from datetime import datetime as time
from statistics import mean

from orm.entity.User import User
from orm.entity.location import Location
from orm.entity.Temperature import Temperature
from orm.entity.humidity import Humidity
from sqlConfig import DBSession


def date_compare(date1, date2, fmt="%Y-%m-%d %H:%M:%S") -> bool:
    """
    比较两个真实日期之间的大小，date1 > date2 则返回True
    :param date1:
    :param date2:
    :param fmt:
    :return:
    """

    zero = datetime.datetime.fromtimestamp(0)

    d1 = datetime.datetime.strptime(str(date1), fmt)

    d2 = datetime.datetime.strptime(str(date2), fmt)

    return d1 >= d2


# 获取前1天或N天的日期，beforeOfDay=1：前1天；beforeOfDay=N：前N天
def getdate(beforeOfDay):
    today = datetime.datetime.now()
    # 计算偏移量
    offset = datetime.timedelta(days=-beforeOfDay)
    # 获取想要的日期的时间
    re_date = (today + offset)
    return re_date


def addNewLocation(dormitoryId, userId, date):
    session = DBSession()
    # 如果数据中存在空值，返回false，或者此用户今天已经报过寝

    today = datetime.datetime.now().strftime("%Y-%m-%d")
    # 记录此user今天是否已经报寝
    flag = False
    tempList = session.query(Location).filter_by(userId=userId).all()
    for i in tempList:
        if i.date.strftime("%Y-%m-%d") == today:
            flag = True

    if dormitoryId is None or userId is None or date is None or flag:
        return False
    else:
        location = Location(dormitoryId, userId, date)
        session.add(location)
        session.commit()
        session.close()
        return True




# 返回今天没有报寝的userId,date为字符串，形如2021-5-15
def getAllUserIdNotInDormitory(date):
    session = DBSession()
    search_date = time.strptime(date, "%Y-%m-%d")
    # 今天报过寝的userId
    userList = session.query(Location).filter_by(date=search_date).all()
    AllUserList = session.query(User).filter_by().all()
    userIdList = []
    for i in userList:
        userIdList.append(i.userId)

    resultList = []
    for i in AllUserList:
        if i.userId not in userIdList:
            resultList.append(i.userId)
    return resultList

def getAllDormitoryIdNotFinish(date):
    userList = getAllUserIdNotInDormitory(date)
    dormitoryList = []
    session = DBSession()
    for i in userList:
        user = session.query(User).filter_by(userId=i).first()
        if user.dormitoryId not in dormitoryList and user.dormitoryId is not None:
            dormitoryList.append(user.dormitoryId)
    return dormitoryList


def addNewTemperature(dormitoryId, temperature, date):
    session = DBSession()
    # 如果数据中存在空值，返回false
    try:
        if dormitoryId is None or temperature is None or date is None:
            return False
        else:
            temperature = Temperature(dormitoryId, temperature, date)
            session.add(temperature)
            session.commit()
            session.close()
            return True

    except:
        return False


def addNewHumidity(dormitoryId, humidity, date):
    session = DBSession()
    # 如果数据中存在空值，返回false
    try:
        if dormitoryId is None or humidity is None or date is None:
            return False
        else:
            humidity = Humidity(dormitoryId, humidity, date)
            session.add(humidity)
            session.commit()
            session.close()
            return True
    except:
        return False


def getAllLocationByUserId(userId):
    session = DBSession()
    locationList = session.query(Location).filter_by(userId=userId).all()
    session.close()
    return locationList


def getAllTemperatureByDormitoryId(dormitoryId):
    session = DBSession()
    temperatureList = session.query(Temperature).filter_by(dormitoryId=dormitoryId).all()
    session.close()
    return temperatureList


def getAllHumidityByDormitoryId(dormitoryId):
    session = DBSession()
    humidityList = session.query(Humidity).filter_by(dormitoryId=dormitoryId).all()
    session.close()
    return humidityList


def getAllTemperatureOfTodayByDormitoryId(dormitoryId):
    date = datetime.datetime.now().strftime("%Y-%m-%d")
    session = DBSession()
    tempList = session.query(Temperature).filter_by(dormitoryId=dormitoryId).all()
    temperatureList = []
    for i in tempList:
        if i.dateTime.strftime("%Y-%m-%d") == date:
            temperatureList.append(i)
    session.close()
    return temperatureList


def getAllHumidityOfTodayByDormitoryId(dormitoryId):
    date = datetime.datetime.now().strftime("%Y-%m-%d")
    session = DBSession()
    tempList = session.query(Humidity).filter_by(dormitoryId=dormitoryId).all()
    humidityList = []
    for i in tempList:
        if i.dateTime.strftime("%Y-%m-%d") == date:
            humidityList.append(i)
    session.close()
    return humidityList


def getATemperatureOf_n_Days(dormitoryId, n):
    today = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    nDaysAgo = getdate(n).strftime("%Y-%m-%d %H:%M:%S")
    session = DBSession()
    tempList = session.query(Temperature).filter_by(dormitoryId=dormitoryId).all()
    temperatureList = []
    lastTem = None

    for i in tempList:

        if date_compare(today, i.dateTime) and date_compare(i.dateTime, nDaysAgo):
            tempDate = i.dateTime.strftime("%Y-%m-%d")
            if tempDate != lastTem:
                temperatureList.append(i.temperature)
            lastTem = tempDate

    session.close()
    return temperatureList


def getAHumidityOf_n_Days(dormitoryId, n):
    today = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    nDaysAgo = getdate(n).strftime("%Y-%m-%d %H:%M:%S")
    session = DBSession()
    tempList = session.query(Humidity).filter_by(dormitoryId=dormitoryId).all()
    HumidityList = []
    lastTem = None

    for i in tempList:

        if date_compare(today, i.dateTime) and date_compare(i.dateTime, nDaysAgo):
            tempDate = i.dateTime.strftime("%Y-%m-%d")
            if tempDate != lastTem:
                HumidityList.append(i.humidity)
            lastTem = tempDate

    session.close()
    return HumidityList
